﻿/*
 * 作者：XXX
 * 功能描述：实现Sql Server数据库的访问与操作
 * 时间：2017-12-1
 * 修改人：
 * 修改时间：
 * 修改描述：
 *
 */

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;


namespace DataAccess
{
    public class SqlHelper
    {
        static SqlConnection conn;

        /// <summary>
        /// 打开数据库的链接
        /// </summary>
        public static void Open()
        {
            try
            {
                string sConnString = ConfigurationManager.ConnectionStrings["ConnStr"].ToString();
                conn = new SqlConnection(sConnString);
                conn.Open();
            }
            catch (Exception ex)
            {
                throw new Exception("SQL Connection Error!");
            }
        }

        /// <summary>执行SQL查询
        /// </summary>
        /// <param name="sSql">SQl语句</param>
        /// <returns>返回查询结果</returns>
        public static DataSet QuerySql(string sSql)
        {
            DataSet ds = new DataSet();
            try
            {
                Open();
                SqlDataAdapter mAdapter = new SqlDataAdapter(sSql, conn);
                mAdapter.Fill(ds);
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                conn.Close();
            }
            return ds;
        }

        /// <summary> 执行带参数的SQL语句，返回数据集
        /// </summary>
        /// <param name="sSql">SQL语句</param>
        /// <param name="Params">参数</param>
        /// <returns>返回查询结果</returns>
        public static DataSet QuerySql(string sSql, SqlParameter[] Params)
        {
            DataSet ds = new DataSet();
            SqlCommand comm = new SqlCommand();
            try
            {
                Open();
                comm.Connection = conn;
                comm.CommandType = CommandType.Text;
                comm.CommandText = sSql;
                comm.Parameters.Clear();
                foreach (SqlParameter p in Params)
                {
                    comm.Parameters.Add(p);
                }
                SqlDataAdapter mAdapter = new SqlDataAdapter(comm);
                mAdapter.Fill(ds);
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                conn.Close();
            }
            return ds;
        }



        /// <summary> 执行存储过程，返回数据集
        /// </summary>
        /// <param name="sPorcName">存储过程</param>
        /// <returns>返回查询结果</returns>
        public static DataSet QueryStoreProc(string sPorcName)
        {
            DataSet ds = new DataSet();
            SqlCommand comm = new SqlCommand();
            try
            {
                Open();
                comm.Connection = conn;
                comm.CommandType = CommandType.StoredProcedure;
                comm.CommandText = sPorcName;
                SqlDataAdapter mAdapter = new SqlDataAdapter(comm);
                mAdapter.Fill(ds);
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                conn.Close();
            }
            return ds;
        }

        /// <summary> 执行带参数的存储过程，返回数据集
        /// </summary>
        /// <param name="sPorcName">存储过程</param>
        /// <param name="Params">参数</param>
        /// <returns>返回查询结果</returns>
        public static DataSet QueryStoreProc(string sPorcName, SqlParameter[] Params)
        {
            DataSet ds = new DataSet();
            SqlCommand comm = new SqlCommand();
            try
            {
                Open();
                comm.Connection = conn;
                comm.CommandType = CommandType.StoredProcedure;
                comm.CommandText = sPorcName;
                comm.Parameters.Clear();
                foreach (SqlParameter p in Params)
                {
                    comm.Parameters.Add(p);
                }
                SqlDataAdapter mAdapter = new SqlDataAdapter(comm);
                mAdapter.Fill(ds);
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                conn.Close();
            }
            return ds;
        }


        /// <summary> 执行SQL语句，返回DataTable
        /// </summary>
        /// <param name="sSql">SQL语句</param>
        /// <returns>返回DataTable对象</returns>
        public static DataTable QuerySqlByDataTable(string sSql)
        {
            DataSet ds = new DataSet();
            try
            {
                Open();
                SqlDataAdapter mAdapter = new SqlDataAdapter(sSql, conn);
                mAdapter.Fill(ds);
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                conn.Close();
            }
            if (ds.Tables[0].Rows.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        /// <summary>执行增删改SQL语句
        /// </summary>
        /// <param name="sSql">SQL语句</param>
        /// <returns>返回影响的行数</returns>
        public static int ExeSql(string sSql)
        {
            int iRet;

            SqlTransaction trans = null;
            try
            {
                Open();
                SqlCommand comm = new SqlCommand(sSql, conn);

                //开启事务
                trans = conn.BeginTransaction("BeginTransaction");
                comm.Transaction = trans;
                iRet = comm.ExecuteNonQuery();
                //提交事务
                trans.Commit();
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                if (trans != null)
                {
                    trans.Rollback();
                }
                throw new Exception(e.Message);
            }
            finally
            {
                conn.Close();
            }
            return iRet;
        }

        /// <summary> 执行带参数的SQL语句
        /// </summary>
        /// <param name="sSql">SQL语句</param>
        /// <param name="Params">参数</param>
        /// <returns>返回影响的行数</returns>
        public static int ExeSql(string sSql, SqlParameter[] Params)
        {
            int iRet;
            SqlTransaction trans = null;
            try
            {
                Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandType = CommandType.Text;
                comm.CommandText = sSql;
                comm.Parameters.Clear();
                foreach (SqlParameter p in Params)
                {
                    comm.Parameters.Add(p);
                }

                //开启事务
                trans = conn.BeginTransaction("BeginTransaction");
                comm.Transaction = trans;

                iRet = comm.ExecuteNonQuery();

                //提交事务
                trans.Commit();
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                if (trans != null)
                {
                    trans.Rollback();
                }
                iRet = 0;
                //throw new Exception(e.Message);

                //return e.Message;
            }
            finally
            {
                conn.Close();
            }
            return iRet;
        }

        /// <summary> 以事务方式执行多条SQL语句
        /// </summary>
        /// <param name="sSql">Sql语句数组</param>
        /// <returns>true：成功 false：失败</returns>
        public static bool ExeSql(string[] sSql)
        {
            Open();
            SqlTransaction trans = conn.BeginTransaction();

            try
            {
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.Transaction = trans;
                foreach (string sql in sSql)
                {
                    comm.CommandType = CommandType.Text;
                    comm.CommandText = sql;
                    comm.ExecuteNonQuery();
                }
                trans.Commit();
                return true;

            }
            catch (System.Data.SqlClient.SqlException e)
            {
                trans.Rollback();
                return false;
                throw new Exception(e.Message);
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>执行SQL语句，返回DataReader对象
        /// </summary>
        /// <param name="sSql">SQL语句</param>
        /// <returns>返回Reader对象</returns>
        public static SqlDataReader ExeSqlReader(string sSql)
        {
            SqlDataReader mReader;
            try
            {
                Open();
                SqlCommand comm = new SqlCommand(sSql, conn);
                mReader = comm.ExecuteReader();
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw new Exception(e.Message);
            }
            //finally
            //{
            //    conn.Close();
            //}
            return mReader;

        }

        /// <summary> 执行SQL语句，返回第一行第一列的值
        /// </summary>
        /// <param name="sSql">SQL语句</param>
        /// <returns>返回第一行第一列的值</returns>
        public static string ExeScalar(string sSql)
        {

            object obj;
            try
            {
                Open();
                SqlCommand comm = new SqlCommand(sSql, conn);
                obj = comm.ExecuteScalar();
                if (obj == null)
                {
                    return "";
                }

            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                conn.Close();
            }
            return obj.ToString();
        }



        /// <summary> 执行SQL语句，返回第一行第一列的值
        /// </summary>
        /// <param name="sSql">SQL语句</param>
        /// <returns>返回第一行第一列的值</returns>
        public static string ExeScalar(string sSql, SqlParameter[] Params)
        {

            object obj;
            try
            {
                Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandType = CommandType.Text;
                comm.CommandText = sSql;
                comm.Parameters.Clear();
                foreach (SqlParameter p in Params)
                {
                    comm.Parameters.Add(p);
                }
                obj = comm.ExecuteScalar();
                if (obj == null)
                {
                    return "";
                }

            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw new Exception(e.Message);
                return "-1";
            }
            finally
            {
                conn.Close();
            }
            return obj.ToString();
        }
    }
}
